/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/07/31
 */
package vn.smartware.co.server.dao.registration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.dto.MstCustomerDTO;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.server.sql.factory.AbstractRegisterCustomerSQLManager;

/**
 * @author thuctt
 * RegisterCustomerSQLManager
 */
public class RegisterCustomerSQLManager extends AbstractRegisterCustomerSQLManager {

	private static Log log = LogFactory.getLog(RegisterCustomerSQLManager.class);

	/**
	 * getMaxCustomerNoSQL
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void getMaxCustomerNoSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(CUSTOMER_NO) AS CUSTOMER_NO ")
		.append(" FROM MSTCUSTOMER ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterCustomerSQL
	 * @param customer
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeRegisterCustomerSQL(MstCustomerDTO customer, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MSTCUSTOMER ( ")
		.append(" NAME, ID_NUMBER, BIRTHDAY, TEL, EMAIL, ADDRESS_CD, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, customer.getName());
			log.info("NAME = " + customer.getName());
			
			ps.setString(i++, customer.getIdNumber());
			log.info("ID_NUMBER = " + customer.getIdNumber());
			
			ps.setString(i++, customer.getBirthday());
			log.info("BIRTHDAY = " + customer.getBirthday());
			
			ps.setString(i++, customer.getTel());
			log.info("TEL = " + customer.getTel());
			
			ps.setString(i++, customer.getEmail());
			log.info("EMAIL = " + customer.getEmail());
			
			ps.setInt(i++, customer.getAddress().getAddressCd());
			log.info("ADDRESS_CD = " + customer.getAddress().getAddressCd());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeUpdateCustomerSQL
	 * @param customer
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeUpdateCustomerSQL(MstCustomerDTO customer, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTCUSTOMER SET ")
		.append(" NAME = ?, ID_NUMBER = ?, BIRTHDAY = ?, TEL = ?, EMAIL = ?, ")
		.append(" UPDATE_USER_ID = ?, UPDATE_DATE = SYSDATE() ")
		.append(" WHERE ")
		.append(" CUSTOMER_NO = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, customer.getName());
			log.info("NAME = " + customer.getName());
			
			ps.setString(i++, customer.getIdNumber());
			log.info("ID_NUMBER = " + customer.getIdNumber());
			
			ps.setString(i++, customer.getBirthday());
			log.info("BIRTHDAY = " + customer.getBirthday());
			
			ps.setString(i++, customer.getTel());
			log.info("TEL = " + customer.getTel());
			
			ps.setString(i++, customer.getEmail());
			log.info("EMAIL = " + customer.getEmail());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setString(i++, customer.getCustomerNo());
			log.info("CUSTOMER_NO = " + customer.getCustomerNo());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeleteCustomerSQL
	 * @param customerNo
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeDeleteCustomerSQL(String customerNo, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTCUSTOMER SET ")
		.append(" DELETE_DATE = SYSDATE() ")
		.append(" WHERE ")
		.append(" CUSTOMER_NO = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, customerNo);
			log.info("CUSTOMER_NO = " + customerNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
}
